-- @author lixiyong01
-- @date 2023.03.14
-- 健康分异常状态
-- 判断异常持续时间，如果连续7天异常，就会进行采取强制措施
-- 2023.08.19 【已下线】

use hdp_lbg_supin_zplisting;

insert overwrite table app_zp_rock_health_score_state partition (dt=${dateSuffix})
select
    a.dimension,
    a.tag,
    a.project,
    a.team,
    a.owner_id,
    a.total_score,
    (nvl(b.state_times,0) + 1) as state_times
from (
    select dimension, tag, project, team, owner_id, total_score
    from hdp_lbg_supin_zplisting.app_zp_rock_health_score_cube
    where dt='${#date(0, 0, -1):yyyyMMdd#}' and total_score<85
) a
left join (
    select dimension, tag, project, team, owner_id, state_times
    from hdp_lbg_supin_zplisting.app_zp_rock_health_score_state
    where dt='${#date(0, 0, -2):yyyyMMdd#}'
) b on a.dimension = b.dimension and a.tag=b.tag and a.project=b.project and a.team=b.team and a.owner_id=b.owner_id;

